package com.mes.spc.util;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;

@SuppressWarnings("unchecked")
public class SPCExcelWriteUtil {
	private Workbook workbook = null;
	private Sheet sheet = null;
	private int rowIndex = 0;
	private Row row = null;
	private CellStyle cellStyle = null;
	
	public void createExcel() {
		workbook = new SXSSFWorkbook(1000);
		sheet = workbook.createSheet();
		createHeadStyle();
	}
	
	public void createExcel(String sheetName) {
		workbook = new SXSSFWorkbook(1000);
		sheet = workbook.createSheet(sheetName);
		createHeadStyle();
	}
	
	private void createHeadStyle() {
		cellStyle = workbook.createCellStyle();
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		
		cellStyle.setFillPattern((short)1);
		cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		
		Font font = workbook.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("宋体");
		font.setFontHeightInPoints((short)9);
		
		cellStyle.setFont(font);
	}
	
	public void createExcel(String[] titles, List<String[]> values) {
		createExcel();
		addRow(titles);
		for (String[] value : values) {
			addRow(value);
		}
	}
	
	public void createHead(SPCExcelHead[][] heads) {
		Cell cell = null;
		SPCExcelHead[] head = null;
		for (int j = 0; j < heads.length; j++) {
			head = heads[j];
			addRow();
			for (int i = 0; i < head.length; i++) {
				cell = row.createCell(i);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(head[i].getText());
				
				if (head[i].getWidth() > 0) {
					sheet.setColumnWidth(i, head[i].getWidth() * 20);
				}
				if (head[i].getColSpan() > 1) {
					//合并单元格
					//sheet.addMergedRegion(new CellRangeAddress());
				}
			}
		}
	}
	
	public void addRow() {
		row = sheet.createRow(rowIndex);
		rowIndex ++;
	}
	
	public void addRow(String []values) {
		addRow();
		for (int j = 0; j < values.length; j++) {
			addCell(j, values[j]);
		}
	}
	
	private void addCell(int index, String value) {
		Cell cell = row.createCell(index);
		if (value != null) {
			cell.setCellValue(value);
		}
	}
	
	public void addSheet(String sheetName) {
		rowIndex = 0;
		sheet = workbook.createSheet(sheetName);
	}
	
	public void addSheet() {
		rowIndex = 0;
		sheet = workbook.createSheet();
	}
	
	public void addImg(File file){
		if(file==null)return;
		try {
			int length = rowIndex;//位置起点
			ByteArrayOutputStream outStream = new ByteArrayOutputStream(); // 将图片写入流中
			BufferedImage bufferImg = ImageIO.read(file);
			ImageIO.write(bufferImg, "PNG", outStream); // 利用HSSFPatriarch将图片写入EXCEL
			Drawing drawing = sheet.createDrawingPatriarch();
			HSSFClientAnchor anchor = new HSSFClientAnchor(length + 200, length + 200, length + 200, length + 200,
			  (short) 0, length + 5, (short) 16, length + 40);
			XSSFClientAnchor anchor1 = new XSSFClientAnchor(length + 100, length + 100, length + 100, length + 100,
			  (short) 0, length + 3, (short) 12, length + 30);
			drawing.createPicture(anchor1, workbook.addPicture(outStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
			
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public void writeFile(String filePath) throws Exception {
		FileOutputStream fileoutputstream = new FileOutputStream(filePath);
		workbook.write(fileoutputstream);
		fileoutputstream.close();
	}

	public void writeFileToResponse(HttpServletResponse response){
		//设置默认文件名为当前时间：年月日时分秒
		String fileName=new SimpleDateFormat("yyyyMMddhhmmss").format(new Date()).toString();
		//设置response头信息
		response.reset();
		response.setContentType("application/vnd.ms-excel");        //改成输出excel文件
		response.setHeader("Content-disposition","attachment; filename="+fileName+".xls" );
		try {
			OutputStream out=response.getOutputStream();
			workbook.write(out);
			out.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public void writeFileToResponse(HttpServletResponse response,String fileName){
		//设置response头信息
		try {
			fileName = URLEncoder.encode(fileName,"UTF8");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		response.reset();
		response.setContentType("application/vnd.ms-excel");        //改成输出excel文件
		response.setHeader("Content-disposition","attachment; filename="+fileName+".xls" );
		try {
			OutputStream out=response.getOutputStream();
			workbook.write(out);
			out.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		try {
			SPCExcelHead[][] heads = new SPCExcelHead[2][6];
			for (int i = 0; i < heads.length; i++) {
				for (int j = 0; j < heads[i].length; j++) {
					heads[i][j] = new SPCExcelHead("表头" + i + "-" + j);
				}
			}
			
			heads[1][0].setWidth(200);
			
			SPCExcelWriteUtil util = new SPCExcelWriteUtil();
			util.createExcel();
			util.createHead(heads);
			util.writeFile("E:/test.xlsx");
			System.out.println("写入成功！");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}